#!/usr/bin/env python3
# -*- coding: utf-8 -*-


import pandas as pd
import numpy as np
from typing import Dict, Any, Optional, List
from datetime import date
import os
import time

from ..config.settings import AppConfig
from ..utils.logger import get_logger
from .data_processor import DataProcessor
from .data_utils_system import (
    calculate_ahs_couponed_price, calculate_zz_couponed_price, 
    calculate_strategy_value, calculate_strategy_quote_price,
    level_order, level_hierarchy, calculate_inversion_statistics,
    calculate_big_gap_value, calculate_big_gap_value_human, 
    define_competiitive_starts_ends, modify_tens_price
)

# 移除重复导入，使用相对导入

import gurobipy as gp
from gurobipy import GRB
from loguru import logger

from threading import Lock
import threading
import queue
from concurrent.futures import ThreadPoolExecutor, as_completed
import uuid


MAX_PRICE_GAP = 0.15  # 最大价差
MIN_SUCCESS_RATIO = 0.1  # 同SKU内不同等级的成交量占比
MAX_ANTI_PRICE = 0  # 防倒挂价格差
LIMIT_ANTI_RATE = 0.05  # 限制倒挂率    


class PriceStrategyEngine:
    """ C2B价格竞争力策略引擎，使用Gurobi求解器 """
    
    def __init__(self, config: AppConfig):
        self.config = config
        self.data_processor = DataProcessor(config)
        
        # 使用从data_utils_system导入的等级定义
        self.level_order = level_order
        self.level_hierarchy = level_hierarchy

    def calculate_strategy(self, product_name, target, target_date, base_data=None, coupon_tag='SELF_1515', custom_coupon_data=None):
        """ 根据指定产品型号、日期和预期竞争力运行BI价格策略 """

        logger.info(f"开始计算{target_date}, 产品 {product_name} 的价格策略，目标竞争力: {target:.2%}")

        try:
            # 创建必要目录
            os.makedirs('collections', exist_ok=True)
            os.makedirs('sku_level', exist_ok=True)

            try:
                # 2025-09-03 获取基础数据，并生成相关价格字段
                if base_data is None:
                    from .database import DatabaseManager
                    db_manager = DatabaseManager(self.config)
                    logger.info(f"正在查询数据库，目标日期: {target_date}, 优惠券类型: {coupon_tag}")
                    base_data = db_manager.get_base_data(target_date, coupon_tag, custom_coupon_data)
                    if base_data is None:
                        logger.error(f"数据库查询失败，目标日期: {target_date}, 优惠券类型: {coupon_tag}")
                        raise ValueError("无法获取基础数据")
                    else:
                        logger.info(f"数据库查询成功，获取到 {len(base_data)} 条基础数据")
                
                # 2025-04-24 用于新版价格平滑v3和v4
                base_data['new_factor'] = 1
                level_to_index = {lvl: i for i, lvl in enumerate(level_order)}
                total_levels = len(level_order)
                concat_df = base_data.groupby(['product_name', 'product_sku_name', 'product_level_name', 'new_factor', 'finalprice']).agg({
                    'success_nums': 'sum',
                    'inquiry_nums': 'sum'
                }).reset_index()
                
                # 筛选产品数据
                logger.info(f"基础数据中包含的产品: {base_data['product_name'].unique().tolist()}")
                df = base_data[base_data['product_name'] == product_name].copy()
                if df.empty:
                    logger.error(f"未找到产品 {product_name} 的数据，可用产品: {base_data['product_name'].unique().tolist()}")
                    raise ValueError(f"未找到产品 {product_name} 的数据")
                else:
                    logger.info(f"找到产品 {product_name} 的数据，共 {len(df)} 条记录")
                df['target'] = target  # 用于区分同一个产品型号，不同竞争力的结果

                product_brand_name = df['product_brand_name'].tolist()[0]
                # 每次循环创建 df 和 unique_combinations 的副本
                df['cost'] = df['standard_price'] * df['success_nums']
                df['cost_daily'] = df['standard_price'] * df['curr_success_nums']
                estimated_total_cost = sum(df['cost'])
                inquiry_nums_30d_total = sum(df['inquiry_nums'])
                success_nums_30d_total = sum(df['success_nums'])
                real_total_cost_daily = sum(df['cost_daily'])
                success_nums_daily_total = sum(df['curr_success_nums']) 

                candidates = []  # 2025-02-18 每个竞争力一个候选集
                df_container = {}
                results = []
                chosen_df = pd.DataFrame()  # 每天的"汇总"结果
                chosen_detailed_df = pd.DataFrame()  # 每天的"明细"结果
                sku_level_df = pd.DataFrame()  # 每天的"sku-level"结果

                ### STEP1: 计算价格策略

                # 寻找能达到 target=XX% 的ratio，初始值为0.80，每次递增0.01
                bi_ratio = 0.80

                while bi_ratio < 1.50:  # 直到找到合适满足求解的 bi_ratio 或者 bi_ratio=1.50才退出 

                    unique_combinations = df[['product_sku_name', 'product_level_name']].drop_duplicates().reset_index(drop=True)
                    # 遍历每个 SKU+级别，生成候选的价格调整方案
                    combination_container = []
                    grouped = df.groupby(['product_sku_name', 'product_level_name'])

                    # 2025-04-29 成交量占比，用于V3的zz_strategy_price筛选，
                    sku_total_success = df.groupby('product_sku_name')['success_nums'].sum().to_dict()

                    for (sku, level), group in grouped:
                        ahs_ori_price = group['finalprice'].iloc[0]
                        ahs_couponed_price = group['ahs_couponed_price'].iloc[0]
                        zz_strategy_prices = group['zz_strategy_price'].unique()

                        # 2025-04-29 第三版V3: 增加限定条件价差，同时考虑不同等级成交量的占比
                        ahs_bi_std_price = group['standard_price'].iloc[0]
                        level_success = group['success_nums'].sum()  # 当前sku-level的成交量
                        total_success = sku_total_success[sku]  # 当前sku的成交量
                        
                        # 避免除零错误
                        if total_success > 0:
                            level_ratio = level_success * 1.0 / total_success
                        else:
                            level_ratio = 0.0  # 如果没有成交量，设置为0
                        # 候选列表，始终保留原始价
                        adjusted_prices = [ahs_ori_price]
                        for zz_price in zz_strategy_prices:

                            # 2025-08-09 增加上限调价限制：动态 BI * ratio
                            new_price_couponed = calculate_ahs_couponed_price(zz_price)
                            # 2025-08-15 剔除 15%的价差比限制，只保留BI基准价的系数限制
                            if new_price_couponed >= ahs_bi_std_price * bi_ratio:
                                continue
                            adjusted_prices.append(zz_price)

                        # Calculate payed_cost and compete_gained for each adjusted price
                        for adjusted_price in adjusted_prices:
                            adjusted_couponed_price = calculate_ahs_couponed_price(adjusted_price)
                            conditioned_b2b_count = group['success_nums'].sum()
                            compete_gained = group[group['zz_couponed_price_18_added'] <= adjusted_couponed_price][
                                'inquiry_nums'].sum()
                            condittioned_price_gap = adjusted_couponed_price - ahs_couponed_price
                            payed_cost = conditioned_b2b_count * condittioned_price_gap

                            # Find the corresponding zz_strategy_price and zz_couponed_price_18_added
                            if adjusted_price in group['zz_strategy_price'].values:
                                zz_strategy_price = adjusted_price
                                zz_couponed_price_18_added = \
                                    group[group['zz_strategy_price'] == adjusted_price]['zz_couponed_price_18_added'].iloc[0]
                            else:
                                zz_strategy_price = ahs_ori_price
                                zz_couponed_price_18_added = ahs_couponed_price

                            # Append the result
                            combination_container.append({
                                'product_sku_name': sku,
                                'product_level_name': level,
                                'key': f"{sku}_{level}_{adjusted_price}",
                                'finalprice': ahs_ori_price,
                                'adjusted_price': adjusted_price,
                                'adjusted_couponed_price': adjusted_couponed_price,
                                'zz_strategy_price': zz_strategy_price,
                                'zz_couponed_price_18_added': zz_couponed_price_18_added,
                                'ahs_couponed_price': ahs_couponed_price,
                                'conditioned_b2b_count': conditioned_b2b_count,
                                'condittioned_price_gap': condittioned_price_gap,
                                'payed_cost': payed_cost,
                                'compete_gained': compete_gained
                            })

                    # Create a DataFrame from the results
                    combination_df = pd.DataFrame(combination_container)

                    # Sort the result DataFrame by ahs_sku_name and level_order
                    combination_df['level_sort'] = combination_df['product_level_name'].apply(lambda x: level_order.index(x) if x in level_order else len(level_order))
                    combination_df = combination_df.sort_values(by=['product_sku_name', 'level_sort']).drop(columns=['level_sort'])

                    # 2025-02-07 用新的求解器 Gurobi 构建混合整数规划模型求解
                    model = gp.Model("Minimize_Cost_Maximize_Competitiveness")

                    # 如果不想在控制台输出求解信息，可设置如下参数：
                    model.Params.OutputFlag = 0
                    # 设置求解器参数：放宽时间限制和最优性差距
                    model.Params.TimeLimit = 600  # 允许求解器运行最长600秒
                    model.Params.MIPGap = 0.001  # 允许0.1%的相对最优性差距

                    # 构造决策变量：对于每个 SKU+级别分组中的每个候选方案建立一个二元变量
                    decision_vars = {}
                    grouped_result_df = combination_df.groupby(['product_sku_name', 'product_level_name'])
                    for (sku, level), group in grouped_result_df:
                        var_dict = {}
                        for idx in group.index:
                            var_name = f"choice_{sku}_{level}_{idx}"
                            var_dict[idx] = model.addVar(vtype=GRB.BINARY, name=var_name)
                        decision_vars[(sku, level)] = var_dict

                    # 更新模型以整合新添加的变量
                    model.update()

                    # 目标函数：最小化总的付费成本
                    objective_expr = gp.quicksum(
                        decision_vars[(sku, level)][i] * combination_df.loc[i, 'payed_cost']
                        for (sku, level), group in grouped_result_df for i in group.index
                    )
                    model.setObjective(objective_expr, GRB.MINIMIZE)

                    # 添加约束：每个 SKU+级别只能选取一个定价方案
                    for (sku, level), group in grouped_result_df:
                        model.addConstr(
                            gp.quicksum(decision_vars[(sku, level)][i] for i in group.index) == 1,
                            name=f"OneChoice_{sku}_{level}"
                        )

                    # 定义总体成本与竞争力（基于原数据计算）
                    total_cost = (df['success_nums'] * df['standard_price']).sum()
                    total_competitive = df['inquiry_nums'].sum()

                    # 约束：所选方案累计获得的竞争力需达到目标比例
                    compete_gained_collect_expr = gp.quicksum(
                        decision_vars[(sku, level)][i] * combination_df.loc[i, 'compete_gained']
                        for (sku, level), group in grouped_result_df for i in group.index
                    )
                    model.addConstr(
                        compete_gained_collect_expr >= target * total_competitive,
                        name="CompetitivenessTarget"
                    )
                    # 求解模型
                    model.optimize()

                    # Check if optimal solution is found
                    if model.status == GRB.OPTIMAL:
                        logger.info(f"Optimal solution found with ratio: {bi_ratio}")
                        break
                    else:
                        logger.info(f"No optimal solution for date: {target_date}, product:  {product_name}, target: {target}, ratio: {bi_ratio}")
                        bi_ratio += 0.01

                # 如果没找到解，说明无法收敛，直接退出循环
                if model.status != GRB.OPTIMAL:
                    logger.info("-------------日期：{}, 产品型号：{}, 竞争力：{}, 没找到解，直接退出循环 ---------------".format(target_date, product_name, target))
                    # continue

                selected_indices = []
                # 遍历所有决策变量，提取取值为 1 的方案对应的候选方案 index
                for (sku, level), var_dict in decision_vars.items():
                    for idx, var in var_dict.items():
                        if var.X > 0.5:  # 二元变量大于0.5视为选中
                            selected_indices.append(idx)
                selected_combine = combination_df.loc[selected_indices]

                # 按照 ahs_sku_name 和 ahs_level_name 进行分组，并统计 c2b_ppv_count 和 b2b_ppv_count 的和
                df_group = df.groupby(['product_name', 'product_sku_name', 'product_level_name', 'sku_level', 'sku_A+', 'finalprice', 'standard_price'], as_index=False)[['inquiry_nums', 'success_nums']].sum()

                # 将sku-level级别的'inquiry_nums', 'success_nums' 加入 unique_combinations
                unique_combinations_re = pd.merge(unique_combinations, df_group, on=['product_sku_name', 'product_level_name'])
                adjusted_prices_df = pd.merge(selected_combine[['product_sku_name', 'product_level_name', 'adjusted_price']], unique_combinations_re, on=['product_sku_name', 'product_level_name'])
                adjusted_prices_df['level_order'] = adjusted_prices_df['product_level_name'].apply(lambda x: level_order.index(x))
                adjusted_prices_df = adjusted_prices_df.sort_values(by=['product_sku_name', 'level_order']).reset_index(drop=True)
                adjusted_prices_df['readjusted_price'] = adjusted_prices_df['adjusted_price']

                # 新版防倒挂：先进行平滑处理，再处理部分细分等级防倒挂
                def vlookup_custom(row):
                    match = adjusted_prices_df[adjusted_prices_df['sku_level'] == row['sku_A+']]
                    if not match.empty:
                        return match['adjusted_price'].tolist()[0]
                    return None
                adjusted_prices_df['同sku的A+券前价'] = adjusted_prices_df.apply(vlookup_custom, axis=1)
                adjusted_prices_df['价差比'] = (adjusted_prices_df['adjusted_price'] - adjusted_prices_df['同sku的A+券前价']) / adjusted_prices_df['同sku的A+券前价'] * (-1)

                ### STEP2: 滑动平滑处理
                # 新版v4
                # --- 1. 计算 score 并标记大等级（同 V3） ---
                def level_rank_func(df, alpha=0.3, beta=0.6, gamma=0.1):
                    df1 = df.copy()
                    df1['level_weight'] = df1['product_level_name'].map(
                        lambda x: (total_levels - level_to_index.get(x, total_levels)) / total_levels
                    )
                    df1['norm_success'] = df1.groupby('product_sku_name')['success_nums'].transform(
                        lambda x: x / x.max() if x.max() > 0 else 0
                    )
                    df1['norm_inquiry'] = df1.groupby('product_sku_name')['inquiry_nums'].transform(
                        lambda x: x / x.max() if x.max() > 0 else 0
                    )
                    df1['eff_norm_success'] = df1['norm_success'] ** df1['new_factor']
                    df1['eff_norm_inquiry'] = df1['norm_inquiry'] ** df1['new_factor']
                    df1['score'] = (
                            alpha * df1['eff_norm_success']
                            + beta * df1['eff_norm_inquiry']
                            + gamma * df1['level_weight']
                    )
                    df1['large_level'] = df1['product_level_name'].str.extract(r'([ABCD])')
                    return df1
                df_scores = level_rank_func(concat_df)

                # --- 2. 挑选每个 (sku, 大等级) 的基准等级 & 合并到 adjusted_prices_df ---
                ref_df = (
                    df_scores.loc[
                        df_scores.groupby(['product_sku_name', 'large_level'])['score'].idxmax(),
                        ['product_sku_name', 'large_level', 'product_level_name', 'finalprice']
                    ].rename(columns={'product_level_name': 'ref_level_name', 'finalprice': 'ref_finalprice'})
                )
                # 主表复制
                result = adjusted_prices_df.copy()
                # 标记大等级
                result['large_level'] = result['product_level_name'].str.extract(r'([ABCD])')

                # 2.1 合并基准等级的 finalprice
                result = result.merge(
                    ref_df,
                    on=['product_sku_name', 'large_level'],
                    how='left'
                )
                # 2.2 合并基准等级的 adjusted_price
                ref_adj = (adjusted_prices_df.rename(columns={'product_level_name': 'ref_level_name', 'adjusted_price': 'ref_adjusted_price'
                                                        })[['product_sku_name', 'ref_level_name', 'ref_adjusted_price']]
                )
                result = result.merge(
                    ref_adj,
                    on=['product_sku_name', 'ref_level_name'],
                    how='left'
                )

                # --- 3. 计算初始 smooth_ratio（同 V3） ---
                # 注意：result 已经包含自身的 finalprice，无需再合并
                result['percentage_of_ref'] = result['finalprice'] / result['ref_finalprice']
                result['lvl_idx'] = result['product_level_name'].map(level_to_index)
                result['ref_idx'] = result['ref_level_name'].map(level_to_index)
                result['smooth_ratio'] = result['percentage_of_ref'] - ((result['lvl_idx'] > result['ref_idx']) * 0.03)

                # --- 4. 记录基准等级原始 adjusted_price，用于跨组联动判断 ---
                result['ref_adj_orig'] = result['ref_adjusted_price']

                # --- 5. 跨大等级联动（V4） ---
                for sku, grp in result.groupby('product_sku_name'):
                    # 只有基准等级自身发生调价才触发
                    pivots = grp[
                        (grp['product_level_name'] == grp['ref_level_name']) &
                        (grp['adjusted_price'] != grp['finalprice'])
                        ]
                    if pivots.empty:
                        continue

                    for _, pivot in pivots.iterrows():
                        pivot_fin = pivot['ref_finalprice']
                        pivot_adj = pivot['ref_adjusted_price']
                        pivot_ori = pivot['ref_adj_orig']
                        # 针对其他大等级的基准行
                        targets = grp[
                            (grp['product_level_name'] == grp['ref_level_name']) &
                            (grp['large_level'] != pivot['large_level'])
                            ]
                        for tidx, target_df in targets.iterrows():
                            ratio_fin = target_df['ref_finalprice'] / pivot_fin
                            smooth_cross = ratio_fin - 0.03
                            old_ratio = target_df['ref_adj_orig'] / pivot_ori
                            if old_ratio < smooth_cross:
                                result.at[tidx, 'ref_adjusted_price'] = pivot_adj * smooth_cross

                # 5.1 **广播**最新基准价到同组其它行
                def _broadcast_ref_price(g):
                    ref_val = g.loc[g["product_level_name"] == g["ref_level_name"],
                    "ref_adjusted_price"].iloc[0]
                    g["ref_adjusted_price"] = ref_val
                    return g
                result = result.groupby(["product_sku_name", "large_level"], group_keys=False).apply(_broadcast_ref_price)

                # --- 6. A+ 特殊“缩差”逻辑（仅大 A 组基准发生调价时） ---
                shrink_rate = 0.6
                skus_A_pivoted = result[
                    (result['large_level'] == 'A') &
                    (result['product_level_name'] == result['ref_level_name']) &
                    (result['adjusted_price'] != result['finalprice'])
                    ]['product_sku_name'].unique()
                mask_A_plus = (
                        (result['large_level'] == 'A') &
                        (result['product_level_name'] == 'A+') &
                        result['product_sku_name'].isin(skus_A_pivoted)
                )
                orig_sr = result.loc[mask_A_plus, 'smooth_ratio']
                result.loc[mask_A_plus, 'smooth_ratio'] = 1 + (orig_sr - 1) * shrink_rate

                # --- 7. 组内平滑调价 & “整十”修正（同 V3） ---
                # 1>. 先生成一个与原价相同的初始列
                result['修差异过大后券前价格'] = result['adjusted_price']
                # 2>. 只对 mask_low = True 的行，做抬升和向整十取整
                mask_low = (result['adjusted_price'] / result['ref_adjusted_price']) < result['smooth_ratio']
                # 3>. 计算抬升后的价格，再取整十
                new_price = (result.loc[mask_low, 'ref_adjusted_price'] * result.loc[mask_low, 'smooth_ratio']).apply(modify_tens_price)
                # 4>. 只替换那些需要抬升的行
                result.loc[mask_low, '修差异过大后券前价格'] = new_price

                # --- 8. 强制：券后价 <= standard_price ---
                def enforce_max_couponed_price(row):
                    price = row['修差异过大后券前价格']
                    std = row['standard_price']
                    # 2025-08-19 券后价要低于当前比例系数下的BI基准价。不断向下“敲”整十，直到券后价 <= bi基准价 * bi_ratio
                    while calculate_ahs_couponed_price(price) > std * bi_ratio:
                        price -= 10
                    return price
                result['修差异过大后券前价格'] = result.apply(enforce_max_couponed_price, axis=1)
                result['修差前后gap'] = (result['修差异过大后券前价格'] - result['adjusted_price']).astype(int)

                # --- 9. 引入3个新字段，然后清理临时列，返回最终结果 ---
                # 9.1 从 concat_df 中把 success_nums 和 inquiry_nums 拉进来
                result = result.merge(
                    concat_df[[
                        'product_sku_name',
                        'product_level_name',
                        'success_nums',
                        'inquiry_nums'
                    ]].rename(columns={
                        'success_nums': 'success_nums_level',
                        'inquiry_nums': 'inquiry_nums_level'
                    }),
                    on=['product_sku_name', 'product_level_name'],
                    how='left'
                )
                # 9.2 用之前的 ref_df 构造 std_level（大等级的基准小等级）
                std_df = (
                    ref_df[['product_sku_name', 'large_level', 'ref_level_name']]
                    .rename(columns={'ref_level_name': 'std_level'})
                )
                result = result.merge(
                    std_df,
                    on=['product_sku_name', 'large_level'],
                    how='left'
                )
                to_drop = [
                    'percentage_of_ref', 'lvl_idx', 'ref_idx',
                    'ref_finalprice', 'ref_adj_orig'
                ]
                result = result.drop(columns=to_drop)
                adjusted_prices_df = result.copy()
                #######---平滑v4结束---######

                # 按照不同ahs_sku_name来处理，优先处理竞争力不下降，同时成本又降低的ahs_sku_name
                # 遍历每个sku
                container = []
                # 所有sku的倒挂都不处理的结果
                anti_prices = adjusted_prices_df["修差异过大后券前价格"].copy()
                adjusted_prices_df["anti_price"] = anti_prices  # 注意：这里只有部分sku的价格调整了
                df_anti0 = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price']], on=['product_sku_name', 'product_level_name'], how='left')
                # Step 2: Calculate adjusted metrics for anti_price
                df_anti0['anti_price_couponed'] = df_anti0['anti_price'].apply(calculate_ahs_couponed_price)
                df_anti0['anti_price_gap'] = df_anti0['anti_price_couponed'] - df_anti0['ahs_couponed_price']
                df_anti0['anti_price_cost'] = df_anti0['anti_price_gap'] * df_anti0['success_nums']
                df_anti0['anti_competitive'] = df_anti0.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                anti_cost_rate0 = sum(df_anti0['anti_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
                anti_competitive0 = sum(df_anti0['anti_competitive']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                # 计算倒挂次数
                adjusted_price_stats0 = calculate_inversion_statistics(adjusted_prices_df, 'adjusted_price')
                # 计算倒挂率
                anti_rate0 = sum(adjusted_price_stats0['inversion_count']) / sum(adjusted_price_stats0['total_comparisons']) if sum(adjusted_price_stats0['total_comparisons']) > 0 else 0
                dic = {
                    'product_sku_name': '空',
                    "anti_cost_rate": anti_cost_rate0,
                    "anti_competitive": anti_competitive0,
                    "anti_rate": anti_rate0,
                    "anti_cost_rate_gap": 0,
                    "anti_competitive_gap": 0,
                    "anti_rate_gap": 0,
                    "anti_gain_vs_competetive_loss": 0,
                }

                for sku_name in adjusted_prices_df['product_sku_name'].unique():

                    # 对于每一次调价都需要初始化
                    anti_prices = adjusted_prices_df["修差异过大后券前价格"].copy()
                    # 遍历每个 “父等级-子等级” 映射关系
                    for parent, child in level_hierarchy:
                        parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                        child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                        if parent_mask.sum() > 0 and child_mask.sum() > 0:
                            parent_idx = adjusted_prices_df[parent_mask].index[0]
                            child_idx = adjusted_prices_df[child_mask].index[0]
                            if anti_prices[parent_idx] <= anti_prices[child_idx]:
                                anti_prices[child_idx] = anti_prices[parent_idx] - MAX_ANTI_PRICE

                    # 计算调整当前 sku_name 调价带来的 成本cost 和 竞争力competitive
                    adjusted_prices_df["anti_price"] = anti_prices  # 注意：这里只有部分sku的价格调整了
                    df_anti = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price']],
                                        on=['product_sku_name', 'product_level_name'], how='left')
                    # Step 2: Calculate adjusted metrics for anti_price
                    df_anti['anti_price_couponed'] = df_anti['anti_price'].apply(calculate_ahs_couponed_price)
                    df_anti['anti_price_gap'] = df_anti['anti_price_couponed'] - df_anti['ahs_couponed_price']
                    df_anti['anti_price_cost'] = df_anti['anti_price_gap'] * df_anti['success_nums']
                    df_anti['anti_competitive'] = df_anti.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                    anti_cost_rate = sum(df_anti['anti_price_cost']) / estimated_total_cost
                    anti_competitive = sum(df_anti['anti_competitive']) / inquiry_nums_30d_total
                    # 计算倒挂率
                    adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'anti_price')
                    anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                    # 差值
                    anti_cost_rate = anti_cost_rate0 - anti_cost_rate
                    anti_rate_gap = anti_rate0 - anti_rate
                    anti_competitive_gap = anti_competitive0 - anti_competitive

                    dic = {
                        'product_sku_name': sku_name,
                        "anti_cost_rate": anti_cost_rate,
                        "anti_competitive": anti_competitive,
                        "anti_rate": anti_rate,
                        "anti_cost_rate_gap": anti_cost_rate,
                        "anti_competitive_gap": anti_competitive_gap,
                        "anti_rate_gap": anti_rate_gap,
                        "anti_gain_vs_competetive_loss": anti_rate_gap / anti_competitive_gap if anti_competitive_gap > 0 else 0
                    }
                    container.append(dic)

                df_set = pd.DataFrame(container)

                # 选出其中 anti_competitive最大 同时anti_cost_rate尽可能最小的 ahs_sku_name
                df_set_sorted = df_set.sort_values(by=['anti_competitive', 'anti_cost_rate'], ascending=[False, True])
                # df_set_sorted.to_excel("{}/{}_df_set_sorted_{}_{}.xlsx".format(sku_date_folder, target_date, product_name, target), index=False)
                # 按照 anti_gain_vs_competetive_loss 排序
                df_set['sort_order'] = df_set['anti_competitive_gap'].apply(lambda x: 0 if x == 0 else 1)
                df_set_sorted2 = df_set.sort_values(by=['sort_order', 'anti_rate_gap', 'anti_gain_vs_competetive_loss'], ascending=[True, False, True]).drop(columns=['sort_order'])  # 删除辅助列
                # df_set_sorted2.to_excel("{}/{}_df_set_sorted2_{}_{}.xlsx".format(sku_date_folder, target_date, product_name, target), index=False)

                sku_names_list = df_set_sorted['product_sku_name'].tolist()
                # logger.info('---------------sku数量为：{} ---------------------'.format(len(sku_names_list)))

                # 方案1：部分倒挂策略1-寻找倒挂率小于人工倒挂率的部分倒挂组合
                for i in range(1, len(df_set_sorted)):

                    sku_names_better = sku_names_list[:i]
                    # 取头部几个sku进行防倒挂处理
                    anti_prices_better = adjusted_prices_df["修差异过大后券前价格"].copy()
                    # 遍历每个 “父等级-子等级” 映射关系
                    for parent, child in level_hierarchy:
                        # 遍历每个sku
                        for sku_name in sku_names_better:
                            parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                            child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                            if parent_mask.sum() > 0 and child_mask.sum() > 0:
                                parent_idx = adjusted_prices_df[parent_mask].index[0]
                                child_idx = adjusted_prices_df[child_mask].index[0]
                                if anti_prices_better[parent_idx] <= anti_prices_better[child_idx]:
                                    anti_prices_better[child_idx] = anti_prices_better[parent_idx] - MAX_ANTI_PRICE

                    # 计算调整当前 sku_name 调价带来的 成本cost 和 竞争力competitive
                    adjusted_prices_df["anti_price_opt"] = anti_prices_better  # 注意：这里只有部分sku的价格调整了
                    df_anti = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price_opt']], on=['product_sku_name', 'product_level_name'], how='left')
                    df_anti['anti_price_couponed'] = df_anti['anti_price_opt'].apply(calculate_ahs_couponed_price)
                    # df_anti['anti_price_couponed'] = calculate_ahs_couponed_price(df_anti, 'anti_price_opt')  # 2025-07-22 更新
                    df_anti['anti_price_gap'] = df_anti['anti_price_couponed'] - df_anti['ahs_couponed_price']
                    df_anti['anti_price_cost'] = df_anti['anti_price_gap'] * df_anti['success_nums']
                    df_anti['anti_competitive'] = df_anti.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                    anti_cost_rate = sum(df_anti['anti_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
                    anti_competitive = sum(df_anti['anti_competitive']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                    # 计算倒挂率
                    adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_opt')
                    anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                    anti_sku_cnt = '{}/{}'.format(len(sku_names_list), len(sku_names_list))

                    # if anti_rate <= LIMIT_ANTI_RATE and anti_competitive >= human_compete_rate and anti_cost_rate <= human_cost_rate:
                    if anti_rate <= LIMIT_ANTI_RATE:
                        anti_sku_cnt = '{}/{}'.format(len(sku_names_better), len(sku_names_list))
                        # logger.info('--------------anti_sku_cnt: {}, anti_rate: {}, LIMIT_ANTI_RATE: {}---------------------'.format(anti_sku_cnt, anti_rate, LIMIT_ANTI_RATE))
                        break
                # 方案1确定最终优化方案价格
                # adjusted_prices_df["anti_price_better"] = anti_prices_better
                # 2025-04-29 加入对价格的尾数修正
                adjusted_prices_df['anti_price_better0'] = anti_prices_better
                adjusted_prices_df['anti_price_better'] = adjusted_prices_df['anti_price_better0'].apply(modify_tens_price)

                # 方案2：部分倒挂策略2-寻找倒挂率小于人工倒挂率的部分倒挂组合
                sku_names_list2 = df_set_sorted2['product_sku_name'].tolist()
                for i in range(1, len(df_set_sorted2)):

                    sku_names_better2 = sku_names_list2[:i]
                    # 取头部2个sku进行防倒挂处理
                    anti_prices_better2 = adjusted_prices_df["修差异过大后券前价格"].copy()
                    # 遍历每个 “父等级-子等级” 映射关系
                    for parent, child in level_hierarchy:
                        # 遍历每个sku
                        for sku_name in sku_names_better2:
                            parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                            child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                            if parent_mask.sum() > 0 and child_mask.sum() > 0:
                                parent_idx = adjusted_prices_df[parent_mask].index[0]
                                child_idx = adjusted_prices_df[child_mask].index[0]
                                if anti_prices_better2[parent_idx] <= anti_prices_better2[child_idx]:
                                    anti_prices_better2[child_idx] = anti_prices_better2[parent_idx] - MAX_ANTI_PRICE

                    # 计算调整当前 sku_name 调价带来的 成本cost 和 竞争力competitive
                    adjusted_prices_df["anti_price_opt2"] = anti_prices_better2  # 注意：这里只有部分sku的价格调整了
                    df_anti = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'anti_price_opt2']], on=['product_sku_name', 'product_level_name'], how='left')
                    df_anti['anti_price_couponed2'] = df_anti['anti_price_opt2'].apply(calculate_ahs_couponed_price)
                    # df_anti['anti_price_couponed2'] = calculate_ahs_couponed_price(df_anti, 'anti_price_opt2')  # 2025-07-22 更新
                    df_anti['anti_price_gap2'] = df_anti['anti_price_couponed2'] - df_anti['ahs_couponed_price']
                    df_anti['anti_price_cost2'] = df_anti['anti_price_gap2'] * df_anti['success_nums']
                    df_anti['anti_competitive2'] = df_anti.apply(lambda x: x['inquiry_nums'] if x['anti_price_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                    anti_cost_rate = sum(df_anti['anti_price_cost2']) / estimated_total_cost if estimated_total_cost > 0 else 0
                    anti_competitive = sum(df_anti['anti_competitive2']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                    # 计算倒挂率
                    adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_opt2')
                    anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                    anti_sku_cnt2 = '{}/{}'.format(len(sku_names_list2), len(sku_names_list2))
                    # if anti_rate <= LIMIT_ANTI_RATE and anti_competitive >= human_compete_rate and anti_cost_rate <= human_cost_rate:
                    if anti_rate <= LIMIT_ANTI_RATE:
                        anti_sku_cnt2 = '{}/{}'.format(len(sku_names_better2), len(sku_names_list2))
                        # logger.info('--------------anti_sku_cnt: {}, anti_rate: {}, LIMIT_ANTI_RATE: {}---------------------'.format(anti_sku_cnt, anti_rate, LIMIT_ANTI_RATE))
                        break
                # 方案2确定最终优化方案价格
                # adjusted_prices_df["anti_price_better2"] = anti_prices_better2
                # 2025-04-29 加入对价格的尾数修正
                adjusted_prices_df['anti_price_better20'] = anti_prices_better2
                adjusted_prices_df['anti_price_better2'] = adjusted_prices_df['anti_price_better20'].apply(modify_tens_price)

                # 全部sku都进行全面的防倒挂处理
                readjusted_prices = adjusted_prices_df["修差异过大后券前价格"].copy()
                # 遍历每个 “父等级-子等级” 映射关系
                for parent, child in level_hierarchy:
                    # 遍历每个sku
                    for sku_name in adjusted_prices_df['product_sku_name'].unique():
                        parent_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == parent)
                        child_mask = (adjusted_prices_df['product_sku_name'] == sku_name) & (adjusted_prices_df["product_level_name"] == child)
                        if parent_mask.sum() > 0 and child_mask.sum() > 0:
                            parent_idx = adjusted_prices_df[parent_mask].index[0]
                            child_idx = adjusted_prices_df[child_mask].index[0]
                            if readjusted_prices[parent_idx] <= readjusted_prices[child_idx]:
                                readjusted_prices[child_idx] = readjusted_prices[parent_idx] - MAX_ANTI_PRICE

                # 2025-04-29 加入对价格的尾数修正
                adjusted_prices_df['readjusted_price0'] = readjusted_prices
                adjusted_prices_df['readjusted_price'] = adjusted_prices_df['readjusted_price0'].apply(modify_tens_price)

                adjusted_prices_df = adjusted_prices_df.sort_values(by=['product_sku_name', "level_order"]).reset_index(drop=True)

                # 计算倒挂率
                adjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'adjusted_price')
                smooth_price_stats = calculate_inversion_statistics(adjusted_prices_df, '修差异过大后券前价格')
                anti_price_stats_bt = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_better')
                anti_price_stats_bt2 = calculate_inversion_statistics(adjusted_prices_df, 'anti_price_better2')
                readjusted_price_stats = calculate_inversion_statistics(adjusted_prices_df, 'readjusted_price')

                # 将 adjusted_price 映射回原来的 DataFrame
                df = df.merge(adjusted_prices_df[['product_sku_name', 'product_level_name', 'level_order', 'adjusted_price', '修差异过大后券前价格', 'std_level', 'success_nums_level', 'inquiry_nums_level', 'anti_price_better', 'anti_price_better2', 'readjusted_price']],
                                on=['product_sku_name', 'product_level_name'], how='left')

                # 计算调整后的券后价
                df['adjusted_price_couponed'] = df['adjusted_price'].apply(calculate_ahs_couponed_price)
                df['smooth_price_couponed'] = df['修差异过大后券前价格'].apply(calculate_ahs_couponed_price)
                df['anti_price_better_couponed'] = df['anti_price_better'].apply(calculate_ahs_couponed_price)
                df['anti_price_better_couponed2'] = df['anti_price_better2'].apply(calculate_ahs_couponed_price)
                df['readjusted_price_couponed'] = df['readjusted_price'].apply(calculate_ahs_couponed_price)

                # 总成本
                df['cost'] = df['standard_price'] * df['success_nums']
                df['cost_daily'] = df['standard_price'] * df['curr_success_nums']

                # 1次优化，添加 adjusted_price_gap, adjusted_price_cost,  cost, competitive_ori, competitive_after字段
                df['adjusted_price_gap'] = df['adjusted_price_couponed'] - df['ahs_couponed_price']
                df['adjusted_price_cost'] = df['adjusted_price_gap'] * df['success_nums']
                df['adjusted_price_cost_daily'] = df['adjusted_price_gap'] * df['curr_success_nums']  #
                df['adjusted_gap_rate'] = df.apply(lambda x: x['adjusted_price_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
                df['cost_rate'] = df.apply(lambda x: x['adjusted_price_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
                df['cost_rate_daily'] = df.apply(lambda x: x['adjusted_price_cost'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
                # 询价竞争力（券后）
                df['inquiry_competitive_after'] = df.apply(lambda x: x['inquiry_nums'] if x['adjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                ### 2025-03-26 新增
                # 成交竞争力（券后）
                df['success_competitive_after'] = df.apply(lambda x: x['success_nums'] if x['adjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                df['success_competitive_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['adjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

                # 1.5 价格平滑处理后的结果
                df['smooth_price_gap'] = df['smooth_price_couponed'] - df['ahs_couponed_price']
                df['smooth_price_cost'] = df['smooth_price_gap'] * df['success_nums']
                df['smooth_price_cost_daily'] = df['smooth_price_gap'] * df['curr_success_nums']  #
                df['smooth_gap_rate'] = df.apply(lambda x: x['smooth_price_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
                df['smooth_cost_rate'] = df.apply(lambda x: x['smooth_price_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
                df['smooth_cost_rate_daily'] = df.apply(lambda x: x['smooth_price_cost_daily'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
                # 询价竞争力（券后）
                df['inquiry_smooth_competitive_after'] = df.apply(lambda x: x['inquiry_nums'] if x['smooth_price_couponed'] >= x['zz_couponed_price_18_added'] else 0,axis=1)
                ### 2025-03-26 新增
                # 成交竞争力（券后）
                df['success_smooth_competitive_after'] = df.apply(lambda x: x['success_nums'] if x['smooth_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                df['success_smooth_competitive_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['smooth_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

                # 部分防倒挂：策略1
                df['anti_price_better_gap'] = df['anti_price_better_couponed'] - df['ahs_couponed_price']
                df['anti_price_better_cost'] = df['anti_price_better_gap'] * df['success_nums']
                df['anti_price_better_cost_daily'] = df['anti_price_better_gap'] * df['curr_success_nums']  #
                df['anti_gap_better_rate'] = df.apply(lambda x: x['anti_price_better_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0,axis=1)
                df['anti_cost_better_rate'] = df.apply(lambda x: x['anti_price_better_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
                df['anti_cost_better_rate_daily'] = df.apply(lambda x: x['anti_price_better_cost_daily'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
                # 询价竞争力（券后）
                df['inquiry_anti_competitive_bt_after'] = df.apply(lambda x: x['inquiry_nums'] if x['anti_price_better_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                ### 2025-03-26 新增
                # 成交竞争力（券后）
                df['success_anti_competitive_bt_after'] = df.apply(lambda x: x['success_nums'] if x['anti_price_better_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                df['success_anti_competitive_bt_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['anti_price_better_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

                # 部分防倒挂：策略2
                df['anti_price_better_gap2'] = df['anti_price_better_couponed2'] - df['ahs_couponed_price']
                df['anti_price_better_cost2'] = df['anti_price_better_gap2'] * df['success_nums']
                df['anti_price_better_cost_daily2'] = df['anti_price_better_gap2'] * df['curr_success_nums']  #
                df['anti_gap_better_rate2'] = df.apply(lambda x: x['anti_price_better_gap2'] / x['standard_price'] if x['standard_price'] != 0 else 0,axis=1)
                df['anti_cost_better_rate2'] = df.apply(lambda x: x['anti_price_better_cost2'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
                df['anti_cost_better_rate_daily2'] = df.apply(lambda x: x['anti_price_better_cost_daily2'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)
                # 询价竞争力（券后）
                df['inquiry_anti_competitive_bt_after2'] = df.apply(lambda x: x['inquiry_nums'] if x['anti_price_better_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                ### 2025-03-26 新增
                # 成交竞争力（券后）
                df['success_anti_competitive_bt_after2'] = df.apply(lambda x: x['success_nums'] if x['anti_price_better_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                df['success_anti_competitive_bt_after_daily2'] = df.apply(lambda x: x['curr_success_nums'] if x['anti_price_better_couponed2'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

                # 最终修正后的结果
                df['readjusted_price_gap'] = df['readjusted_price_couponed'] - df['ahs_couponed_price']
                df['readjusted_price_cost'] = df['readjusted_price_gap'] * df['success_nums']
                df['readjusted_price_cost_daily'] = df['readjusted_price_gap'] * df['curr_success_nums']  #
                df['readjusted_gap_rate'] = df.apply(lambda x: x['readjusted_price_gap'] / x['standard_price'] if x['standard_price'] != 0 else 0, axis=1)
                df['recost_rate'] = df.apply(lambda x: x['readjusted_price_cost'] / x['cost'] if x['cost'] != 0 else 0, axis=1)
                df['recost_rate_daily'] = df.apply(lambda x: x['readjusted_price_cost_daily'] / x['cost_daily'] if x['cost_daily'] != 0 else 0, axis=1)  #
                # 询价竞争力（券后）
                df['inquiry_recompetitive_after'] = df.apply(lambda x: x['inquiry_nums'] if x['readjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                ### 2025-03-26 新增
                # 成交竞争力（券后）
                df['success_recompetitive_after'] = df.apply(lambda x: x['success_nums'] if x['readjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
                df['success_recompetitive_after_daily'] = df.apply(lambda x: x['curr_success_nums'] if x['readjusted_price_couponed'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

                # 对结果进行排序
                df = df.sort_values(by=['product_sku_name', 'level_order', 'zz_strategy_price'], ascending=[True, True, False]).reset_index(drop=True)

                # df_container[str(target)] = df
                df_container[target] = df

                # 预估成本率(30天)
                real_total_cost_rate = sum(df['adjusted_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
                smooth_total_cost_rate = sum(df['smooth_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
                anti_total_cost_bt_rate = sum(df['anti_price_better_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0
                anti_total_cost_bt_rate2 = sum(df['anti_price_better_cost2']) / estimated_total_cost if estimated_total_cost > 0 else 0
                re_real_total_cost_rate = sum(df['readjusted_price_cost']) / estimated_total_cost if estimated_total_cost > 0 else 0

                # 当日真实成本率
                real_total_cost_rate_daily = sum(df['adjusted_price_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
                smooth_total_cost_rate_daily = sum(df['smooth_price_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
                anti_total_cost_bt_rate_daily = sum(df['anti_price_better_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
                anti_total_cost_bt_rate_daily2 = sum(df['anti_price_better_cost_daily2']) / real_total_cost_daily if real_total_cost_daily > 0 else 0
                re_real_total_cost_rate_daily = sum(df['readjusted_price_cost_daily']) / real_total_cost_daily if real_total_cost_daily > 0 else 0

                # 询价竞争力
                inquiry_real_total_compete_rate = sum(df['inquiry_competitive_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                inquiry_smooth_total_compete_rate = sum(df['inquiry_smooth_competitive_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                inquiry_anti_total_compete_bt_rate = sum(df['inquiry_anti_competitive_bt_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                inquiry_anti_total_compete_bt_rate2 = sum(df['inquiry_anti_competitive_bt_after2']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0
                inquiry_re_real_total_compete_rate = sum(df['inquiry_recompetitive_after']) / inquiry_nums_30d_total if inquiry_nums_30d_total > 0 else 0

                ###  2025-03-26 新增
                # 成交竞争力（30天预估）
                success_real_total_compete_rate = sum(df['success_competitive_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
                success_smooth_total_compete_rate = sum(df['success_smooth_competitive_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
                success_anti_total_compete_bt_rate = sum(df['success_anti_competitive_bt_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
                success_anti_total_compete_bt_rate2 = sum(df['success_anti_competitive_bt_after2']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
                success_re_real_total_compete_rate = sum(df['success_recompetitive_after']) / success_nums_30d_total if success_nums_30d_total > 0 else 0
                # 成交竞争力（实际）
                success_real_total_compete_rate_daily = sum(df['success_competitive_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
                success_smooth_total_compete_rate_daily = sum(df['success_smooth_competitive_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
                success_anti_total_compete_bt_rate_daily = sum(df['success_anti_competitive_bt_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
                success_anti_total_compete_bt_rate_daily2 = sum(df['success_anti_competitive_bt_after_daily2']) / success_nums_daily_total if success_nums_daily_total > 0 else 0
                success_re_real_total_compete_rate_daily = sum(df['success_recompetitive_after_daily']) / success_nums_daily_total if success_nums_daily_total > 0 else 0

                # 倒挂率
                anti_rate = sum(adjusted_price_stats['inversion_count']) / sum(adjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                smooth_anti_rate = sum(smooth_price_stats['inversion_count']) / sum(smooth_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                real_anti_bt_rate = sum(anti_price_stats_bt['inversion_count']) / sum(anti_price_stats_bt['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                real_anti_bt_rate2 = sum(anti_price_stats_bt2['inversion_count']) / sum(anti_price_stats_bt2['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0
                reanti_rate = sum(readjusted_price_stats['inversion_count']) / sum(readjusted_price_stats['total_comparisons']) if sum(adjusted_price_stats['total_comparisons']) > 0 else 0

                results.append({
                    'inquiry_nums_30d_total': inquiry_nums_30d_total,
                    'success_nums_30d_total': success_nums_30d_total,
                    'success_nums_daily_total': success_nums_daily_total,  #
                    'target': f"{target:.2%}",
                    'product_name': product_name,
                    'estimated_total_cost': estimated_total_cost,
                    'real_total_cost_daily': real_total_cost_daily,  #

                    # 模型结果
                    'anti_rate': '0',
                    'real_total_cost_rate': f"{real_total_cost_rate:.2%}",
                    'real_total_cost_rate_daily': f"{real_total_cost_rate_daily:.2%}",  #
                    'inquiry_real_total_compete_rate': f"{inquiry_real_total_compete_rate:.2%}",
                    'success_real_total_compete_rate': f"{success_real_total_compete_rate:.2%}",
                    'success_real_total_compete_rate_daily': f"{success_real_total_compete_rate_daily:.2%}",
                    # 平滑后结果
                    'smooth_anti_rate': '0',
                    'smooth_total_cost_rate': f"{smooth_total_cost_rate:.2%}",
                    'smooth_total_cost_rate_daily': f"{smooth_total_cost_rate_daily:.2%}",  #
                    'inquiry_smooth_total_compete_rate': f"{inquiry_smooth_total_compete_rate:.2%}",
                    'success_smooth_total_compete_rate': f"{success_smooth_total_compete_rate:.2%}",
                    'success_smooth_total_compete_rate_daily': f"{success_smooth_total_compete_rate_daily:.2%}",
                    # 低于人工的多个sku防倒挂：策略1
                    'anti_sku_cnt': anti_sku_cnt,
                    'real_anti_bt_rate': f"{real_anti_bt_rate:.2%}",
                    'anti_total_cost_bt_rate': f"{anti_total_cost_bt_rate:.2%}",
                    'anti_total_cost_bt_rate_daily': f"{anti_total_cost_bt_rate_daily:.2%}",  #
                    'inquiry_anti_total_compete_bt_rate': f"{inquiry_anti_total_compete_bt_rate:.2%}",
                    'success_anti_total_compete_bt_rate': f"{success_anti_total_compete_bt_rate:.2%}",
                    'success_anti_total_compete_bt_rate_daily': f"{success_anti_total_compete_bt_rate_daily:.2%}",
                    # 低于人工的多个sku防倒挂：策略2
                    'anti_sku_cnt2': anti_sku_cnt2,
                    'real_anti_bt_rate2': f"{real_anti_bt_rate2:.2%}",
                    'anti_total_cost_bt_rate2': f"{anti_total_cost_bt_rate2:.2%}",
                    'anti_total_cost_bt_rate_daily2': f"{anti_total_cost_bt_rate_daily2:.2%}",  #
                    'inquiry_anti_total_compete_bt_rate2': f"{inquiry_anti_total_compete_bt_rate2:.2%}",
                    'success_anti_total_compete_bt_rate2': f"{success_anti_total_compete_bt_rate2:.2%}",
                    'success_anti_total_compete_bt_rate_daily2': f"{success_anti_total_compete_bt_rate_daily2:.2%}",
                    # 全部防倒挂
                    'reanti_rate': '0',
                    're_real_total_cost_rate': f"{re_real_total_cost_rate:.2%}",
                    're_real_total_cost_rate_daily': f"{re_real_total_cost_rate_daily:.2%}",
                    'inquiry_re_real_total_compete_rate': f"{inquiry_re_real_total_compete_rate:.2%}",
                    'success_re_real_total_compete_rate': f"{success_re_real_total_compete_rate:.2%}",
                    'success_re_real_total_compete_rate_daily': f"{success_re_real_total_compete_rate_daily:.2%}"
                })
                
                ### 2025-03-26 新增
                #   1、基础数据：询价量、成交量
                #   2、目标数据：询价竞争力、成交竞争力、ahs追后券后价、zz追后券后价、价差金额（ahs-zz）、价差比（（ahs-zz/zz）-1）
                # AHS
                # ahs_adjust_weighted_price = int( sum(df['adjusted_price_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))
                ahs_smooth_weighted_price = int(sum(df['smooth_price_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))   # AHS权重价格1
                ahs_anti1_weighted_price = int(sum(df['anti_price_better_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格2
                ahs_anti2_weighted_price = int(sum(df['anti_price_better_couponed2'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格3
                ahs_readjust_weighted_price = int(sum(df['readjusted_price_couponed'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001))  # AHS权重价格4
                zz_weighted_price = int(sum(df['zz_couponed_price_18_added'] * df['inquiry_nums']) / (inquiry_nums_30d_total + 0.0001)) # ZZ 权重价格

                # 2025-04-01 增加3个拆分成本率：Bi调价成本率、平滑成本率、倒挂成本率
                bi_estimated_splited_cost_rate = sum((df['adjusted_price_couponed'] - df['ahs_couponed_price']) * df['success_nums']) / estimated_total_cost if estimated_total_cost > 0 else 0
                smooth_estimated_splited_cost_rate = sum((df['smooth_price_couponed'] - df['adjusted_price_couponed']) * df['success_nums']) / estimated_total_cost if estimated_total_cost > 0 else 0

                if LIMIT_ANTI_RATE >= smooth_anti_rate:
                    candidates.append({
                        'partition_date': target_date,
                        'brand_name': product_brand_name,
                        'product_name': product_name,
                        'estimated_total_cost': estimated_total_cost,
                        'real_total_cost_daily': real_total_cost_daily,  #
                        'target': target,
                        'type': 'smooth',
                        'anti_rate': smooth_anti_rate,
                        'cost_rate_estimated': smooth_total_cost_rate,
                        'cost_rate_real': smooth_total_cost_rate_daily,
                        'inquiry_compete_rate': inquiry_smooth_total_compete_rate,
                        'success_compete_rate': success_smooth_total_compete_rate,
                        'success_compete_rate_daily': success_smooth_total_compete_rate_daily,
                        # 2025-03-26 新增
                        'inquiry_nums_30d_total':inquiry_nums_30d_total,  # 30天询价量
                        'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                        'ahs_weighted_price': ahs_smooth_weighted_price,
                        'zz_weighted_price': zz_weighted_price,
                        'weighted_price_gap': ahs_smooth_weighted_price - zz_weighted_price,
                        'weighted_price_gap_rate': ((ahs_smooth_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                    })


                if LIMIT_ANTI_RATE >= real_anti_bt_rate:
                    candidates.append({
                        'partition_date': target_date,
                        'brand_name': product_brand_name,
                        'product_name': product_name,
                        'estimated_total_cost': estimated_total_cost,
                        'real_total_cost_daily': real_total_cost_daily,  #
                        'target': target,
                        'type': 'anti1',
                        'anti_rate': real_anti_bt_rate,
                        'cost_rate_estimated': anti_total_cost_bt_rate,
                        'cost_rate_real': anti_total_cost_bt_rate_daily,
                        'inquiry_compete_rate': inquiry_anti_total_compete_bt_rate,
                        'success_compete_rate': success_anti_total_compete_bt_rate,  # ???
                        'success_compete_rate_daily': success_anti_total_compete_bt_rate_daily,
                        # 2025-03-26 新增
                        'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                        'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                        'ahs_weighted_price': ahs_anti1_weighted_price,
                        'zz_weighted_price': zz_weighted_price,
                        'weighted_price_gap': ahs_anti1_weighted_price - zz_weighted_price,
                        'weighted_price_gap_rate': ((ahs_anti1_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                    })


                if LIMIT_ANTI_RATE >= real_anti_bt_rate2:
                    candidates.append({
                        'partition_date': target_date,
                        'brand_name': product_brand_name,
                        'product_name': product_name,
                        'estimated_total_cost': estimated_total_cost,
                        'real_total_cost_daily': real_total_cost_daily,  #
                        'target': target,
                        'type': 'anti2',
                        'anti_rate': real_anti_bt_rate2,
                        'cost_rate_estimated': anti_total_cost_bt_rate2,
                        'cost_rate_real': anti_total_cost_bt_rate_daily2,
                        'inquiry_compete_rate': inquiry_anti_total_compete_bt_rate2,
                        'success_compete_rate': success_anti_total_compete_bt_rate2,
                        'success_compete_rate_daily': success_anti_total_compete_bt_rate_daily2,
                        # 2025-03-26 新增
                        'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                        'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                        'ahs_weighted_price': ahs_anti2_weighted_price,
                        'zz_weighted_price': zz_weighted_price,
                        'weighted_price_gap': ahs_anti2_weighted_price - zz_weighted_price,
                        'weighted_price_gap_rate': ((ahs_anti2_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                    })

                if LIMIT_ANTI_RATE >= reanti_rate:
                    candidates.append({
                        'partition_date': target_date,
                        'brand_name': product_brand_name,
                        'product_name': product_name,
                        'estimated_total_cost': estimated_total_cost,
                        'real_total_cost_daily': real_total_cost_daily,  #
                        'target': target,
                        'type': 'reanti',
                        'anti_rate': reanti_rate,
                        'cost_rate_estimated': re_real_total_cost_rate,
                        'cost_rate_real': re_real_total_cost_rate_daily,
                        'inquiry_compete_rate': inquiry_re_real_total_compete_rate,
                        'success_compete_rate': success_re_real_total_compete_rate,
                        'success_compete_rate_daily': success_re_real_total_compete_rate_daily,
                        # 2025-03-26 新增
                        'inquiry_nums_30d_total': inquiry_nums_30d_total,  # 30天询价量
                        'success_nums_30d_total': success_nums_30d_total,  # 30天成交量
                        'ahs_weighted_price': ahs_readjust_weighted_price,
                        'zz_weighted_price': zz_weighted_price,
                        'weighted_price_gap': ahs_readjust_weighted_price - zz_weighted_price,
                        'weighted_price_gap_rate': ((ahs_readjust_weighted_price - zz_weighted_price) * 0.001) / (zz_weighted_price * 0.001)
                    })

                candidates_df = pd.DataFrame(candidates)

                # 2025-04-01 新增3个拆分成本率
                candidates_df['bi_estimated_splited_cost_rate'] = bi_estimated_splited_cost_rate
                candidates_df['smooth_estimated_splited_cost_rate'] = smooth_estimated_splited_cost_rate
                candidates_df['anti_estimated_splited_cost_rate'] = candidates_df['cost_rate_estimated'] - bi_estimated_splited_cost_rate - smooth_estimated_splited_cost_rate


                if len(candidates_df) == 0:
                    logger.info('---------------没找到符合倒挂率的候选集candidates-----------------')
                    # continue

                # 2025-02-18 从当前竞争力中的候选集中，选择成本最小的那个
                candidates_df_sorted = candidates_df.sort_values(by='cost_rate_estimated', ascending=True)
                candidates_head = candidates_df_sorted.head(1)

                # 2025-08-14 新增BI价比率系数
                candidates_head['bi_ratio'] = bi_ratio
                # 从 candidates_df_sorted 中选出第一条数据并合并到 chosen_df
                chosen_df = pd.concat([chosen_df, candidates_head], ignore_index=True)

                # 输出选中的candidates中的 target 和 type
                chosen_candidate_target = candidates_df_sorted['target'].tolist()[0]
                chosen_candidate_type = candidates_df_sorted['type'].tolist()[0]

                # the_df = df_container[chosen_candidate_target]
                the_df = df  # 2025-02-18 从当前df中筛选出相应的数据结果 通过type来选定最终的df取值

                # 2025-04-01 增加5个价格和3个tag, 用于区分是否BI调价、是否平滑、是否防倒挂
                the_df['bi_price'] = the_df['adjusted_price']
                the_df['smooth_price'] = the_df['修差异过大后券前价格']
                the_df['anti1_price'] = the_df['anti_price_better']
                the_df['anti2_price'] = the_df['anti_price_better2']
                the_df['reanti_price'] = the_df['readjusted_price']

                # the_df['bi_tag'] = 1 if the_df['adjusted_price'] == the_df['finalprice'] else 0
                # the_df['smooth_tag'] = 1 if the_df['修差异过大后券前价格'] == the_df['adjusted_price'] else 0
                the_df['bi_tag'] = np.where(the_df['adjusted_price'] == the_df['finalprice'], 1, 0)
                the_df['smooth_tag'] = np.where(the_df['修差异过大后券前价格'] == the_df['adjusted_price'], 1, 0)
                # the_df['deanti_tag'] = 0  补在后面

                if chosen_candidate_type == 'smooth':
                    the_df['chosen_type'] = 'smooth'
                    the_df['chosen_price'] = the_df['修差异过大后券前价格']
                    the_df['chosen_price_couponed'] = the_df['smooth_price_couponed']
                    the_df['chosen_price_gap'] = the_df['smooth_price_gap']
                    the_df['chosen_price_cost'] = the_df['smooth_price_cost']
                    the_df['chosen_price_cost_daily'] = the_df['smooth_price_cost_daily']
                    the_df['chosen_gap_rate'] = the_df['smooth_gap_rate']
                    the_df['chosen_cost_rate'] = the_df['smooth_cost_rate']
                    the_df['chosen_cost_rate_daily'] = the_df['smooth_cost_rate_daily']
                    the_df['chosen_competetive'] = the_df['inquiry_smooth_competitive_after']
                    the_df['chosen_competetive_success'] = the_df['success_smooth_competitive_after']
                    the_df['chosen_competetive_success_daily'] = the_df['success_smooth_competitive_after_daily']
                if chosen_candidate_type == 'anti1':
                    the_df['chosen_type'] = 'anti1'
                    the_df['chosen_price'] = the_df['anti_price_better']
                    the_df['chosen_price_couponed'] = the_df['anti_price_better_couponed']
                    the_df['chosen_price_gap'] = the_df['anti_price_better_gap']
                    the_df['chosen_price_cost'] = the_df['anti_price_better_cost']
                    the_df['chosen_price_cost_daily'] = the_df['anti_price_better_cost_daily']
                    the_df['chosen_gap_rate'] = the_df['anti_gap_better_rate']
                    the_df['chosen_cost_rate'] = the_df['anti_cost_better_rate']
                    the_df['chosen_cost_rate_daily'] = the_df['anti_cost_better_rate_daily']
                    the_df['chosen_competetive'] = the_df['inquiry_anti_competitive_bt_after']
                    the_df['chosen_competetive_success'] = the_df['success_anti_competitive_bt_after']
                    the_df['chosen_competetive_success_daily'] = the_df['success_anti_competitive_bt_after_daily']
                if chosen_candidate_type == 'anti2':
                    the_df['chosen_type'] = 'anti2'
                    the_df['chosen_price'] = the_df['anti_price_better2']
                    the_df['chosen_price_couponed'] = the_df['anti_price_better_couponed2']
                    the_df['chosen_price_gap'] = the_df['anti_price_better_gap2']
                    the_df['chosen_price_cost'] = the_df['anti_price_better_cost2']
                    the_df['chosen_price_cost_daily'] = the_df['anti_price_better_cost_daily2']
                    the_df['chosen_gap_rate'] = the_df['anti_gap_better_rate2']
                    the_df['chosen_cost_rate'] = the_df['anti_cost_better_rate2']
                    the_df['chosen_cost_rate_daily'] = the_df['anti_cost_better_rate_daily2']
                    the_df['chosen_competetive'] = the_df['inquiry_anti_competitive_bt_after2']
                    the_df['chosen_competetive_success'] = the_df['success_anti_competitive_bt_after2']
                    the_df['chosen_competetive_success_daily'] = the_df['success_anti_competitive_bt_after_daily2']
                if chosen_candidate_type == 'reanti':
                    the_df['chosen_type'] = 'reanti'
                    the_df['chosen_price'] = the_df['readjusted_price']
                    the_df['chosen_price_couponed'] = the_df['readjusted_price_couponed']
                    the_df['chosen_price_gap'] = the_df['readjusted_price_gap']
                    the_df['chosen_price_cost'] = the_df['readjusted_price_cost']
                    the_df['chosen_price_cost_daily'] = the_df['readjusted_price_cost_daily']
                    the_df['chosen_gap_rate'] = the_df['readjusted_gap_rate']
                    the_df['chosen_cost_rate'] = the_df['recost_rate']
                    the_df['chosen_cost_rate_daily'] = the_df['recost_rate_daily']
                    the_df['chosen_competetive'] = the_df['inquiry_recompetitive_after']
                    the_df['chosen_competetive_success'] = the_df['success_recompetitive_after']
                    the_df['chosen_competetive_success_daily'] = the_df['success_recompetitive_after_daily']

                # 接上面新增的tag
                the_df['deanti_tag'] = np.where(the_df['chosen_price'] == the_df['修差异过大后券前价格'], 1, 0)

                chosen_detailed_df = pd.concat([chosen_detailed_df, the_df], ignore_index=True)

                # if len(the_df) > 0:
                sku_level = the_df[[
                    'partition_date', 'target', 'product_category_name', 'product_brand_name', 'product_name', 'product_sku_name', 'product_level_name', 'group_name',
                    'product_category_id', 'product_brand_id', 'product_id', 'product_sku_id', 'product_level_id', 'group_id',
                    'finalprice', 'ahs_couponed_price', 'standard_price', 'human_price', 'chosen_price', 'chosen_price_couponed',
                    # 2025-04-01 增加5个价格和3个tag
                    'bi_price', 'smooth_price', 'anti1_price', 'anti2_price', 'reanti_price', 'bi_tag', 'smooth_tag', 'deanti_tag',
                    # 2025-07-16 增加3个字段：大等级对应的基准等级，sku-level的询价量，sku-level的成交量
                    'std_level', 'success_nums_level', 'inquiry_nums_level'
                ]]

                # 通过 partition_date + sku + level + group 去重
                sku_level.drop_duplicates(inplace=True)
                # 拼接sku-level的价格进入
                sku_level_df = pd.concat([sku_level_df, sku_level], ignore_index=True)

                selected_chosen_results = chosen_detailed_df[['partition_date', 'target', 'product_brand_name', 'product_id', 'product_name',
                    'product_level_id', 'product_level_name', 'product_sku_id', 'product_sku_name', 'curr_success_nums', 'success_nums', 'inquiry_nums',
                    'zz_pre_coupon_price', 'zz_couponed_price_18_added', 'zz_strategy_price', 'zz_strategy_price_couponed', 'human_price', 'finalprice',
                    'ahs_couponed_price',
                    'chosen_price', 'chosen_price_couponed', 'chosen_price_gap', 'chosen_price_cost', 'chosen_price_cost_daily', 'chosen_gap_rate', 'chosen_cost_rate',
                    'chosen_cost_rate_daily', 'chosen_competetive',
                    'chosen_competetive_success', 'chosen_competetive_success_daily'  # 2025-03-26 新增
                ]]

                # 添加调试信息
                logger.info(f"产品 {product_name} 处理完成:")
                logger.info(f"  - chosen_df 形状: {chosen_df.shape}")
                logger.info(f"  - sku_level_df 形状: {sku_level_df.shape}")
                logger.info(f"  - selected_chosen_results 形状: {selected_chosen_results.shape}")
                
                # 检查数据是否为空，如果为空则返回基础数据
                if chosen_df.empty:
                    logger.warning(f"产品 {product_name} 没有找到合适的候选集，返回基础数据")
                    # 返回基础数据作为备选
                    if 'df' in locals() and not df.empty:
                        logger.info(f"  - 返回基础数据 df 形状: {df.shape}")
                        return {
                            "success": True,
                            "message": "成功获取产品基础数据（无优化结果）",
                            "data": df,
                            "sku_level_df": sku_level_df,
                            "selected_chosen_results": selected_chosen_results
                        }
                    else:
                        logger.error(f"  - 基础数据 df 也不存在或为空")
                        return {
                            "success": False,
                            "message": f"产品 {product_name} 没有找到任何数据",
                            "data": None
                        }
                
                # 为简化示例，这里只返回数据
                return {
                    "success": True,
                    "message": "成功获取产品数据",
                    "data": chosen_df,
                    "sku_level_df": sku_level_df,
                    "selected_chosen_results": selected_chosen_results
                }

            except Exception as e:
                return {
                    "success": False,
                    "message": f"查询产品数据时出错: {e}",
                    "data": None
                }

        except Exception as e:
            return {
                "success": False,
                "message": f"处理时出错: {e}",
                "data": None
            }

    
    def _preprocess_product_data(self, data: pd.DataFrame, competitiveness: float) -> pd.DataFrame:
        """预处理产品数据"""

        try:
            # 计算成本
            data['cost'] = data['standard_price'] * data['success_nums']
            data['cost_daily'] = data['standard_price'] * data['curr_success_nums']
            
            # 设置目标竞争力
            data['target'] = competitiveness
            
            # 计算券后价格（这里需要调用券计算函数）
            # 暂时使用简化版本
            data['ahs_couponed_price'] = data['finalprice'] * 1.1  # 简化处理
            data['zz_couponed_price_18_added'] = data['zz_pre_coupon_price'] * 1.08  # 简化处理
            
            # 计算价差
            data['券后价差-ahs-转转'] = data['ahs_couponed_price'] - data['zz_couponed_price_18_added']
            
            logger.info("产品数据预处理完成")
            return data
            
        except Exception as e:
            logger.error(f"产品数据预处理失败: {str(e)}")
            raise
    
    def _generate_price_candidates(self, data: pd.DataFrame) -> List[Dict[str, Any]]:
        """生成候选价格方案"""
        candidates = []
        
        try:
            # 按SKU和等级分组
            grouped = data.groupby(['product_sku_name', 'product_level_name'])
            
            for (sku, level), group in grouped:
                ahs_ori_price = group['finalprice'].iloc[0]
                ahs_couponed_price = group['ahs_couponed_price'].iloc[0]
                zz_strategy_prices = group['zz_strategy_price'].unique()
                ahs_bi_std_price = group['standard_price'].iloc[0]
                
                # 生成候选价格
                adjusted_prices = self._calculate_adjusted_prices(
                    ahs_ori_price, zz_strategy_prices, ahs_bi_std_price
                )
                
                # 为每个候选价格计算指标
                for adjusted_price in adjusted_prices:
                    candidate = self._evaluate_price_candidate(
                        group, adjusted_price, ahs_couponed_price
                    )
                    candidate.update({
                        'product_sku_name': sku,
                        'product_level_name': level,
                        'adjusted_price': adjusted_price
                    })
                    candidates.append(candidate)
            
            logger.info(f"生成了 {len(candidates)} 个候选价格方案")
            return candidates
            
        except Exception as e:
            logger.error(f"生成候选价格方案失败: {str(e)}")
            raise
    
    def _calculate_adjusted_prices(
        self, 
        ahs_ori_price: float, 
        zz_strategy_prices: np.ndarray, 
        ahs_bi_std_price: float
    ) -> List[float]:
        """计算调整后的价格"""

        adjusted_prices = [ahs_ori_price]
        
        if ahs_ori_price >= zz_strategy_prices.max():
            # 情况1：AHS原价 >= 所有转转策略价
            pass
        elif ahs_ori_price <= zz_strategy_prices.min():
            # 情况2：AHS原价 <= 所有转转策略价
            for zz_price in zz_strategy_prices:
                if (zz_price - ahs_ori_price) / ahs_bi_std_price <= self.max_price_gap:
                    adjusted_prices.append(zz_price)
        else:
            # 情况3：AHS原价在转转策略价之间
            for zz_price in zz_strategy_prices:
                if (zz_price > ahs_ori_price and 
                    (zz_price - ahs_ori_price) / ahs_bi_std_price <= self.max_price_gap):
                    adjusted_prices.append(zz_price)
        
        return adjusted_prices
    
    def _evaluate_price_candidate(
        self, 
        group: pd.DataFrame, 
        adjusted_price: float, 
        ahs_couponed_price: float
    ) -> Dict[str, Any]:
        """评估价格候选方案"""

        adjusted_couponed_price = adjusted_price * 1.1  # 简化券后价计算
        conditioned_b2b_count = group['success_nums'].sum()
        compete_gained = group[group['zz_couponed_price_18_added'] <= adjusted_couponed_price]['inquiry_nums'].sum()
        conditioned_price_gap = adjusted_couponed_price - ahs_couponed_price
        payed_cost = conditioned_b2b_count * conditioned_price_gap
        
        return {
            'adjusted_couponed_price': adjusted_couponed_price,
            'conditioned_b2b_count': conditioned_b2b_count,
            'compete_gained': compete_gained,
            'conditioned_price_gap': conditioned_price_gap,
            'payed_cost': payed_cost
        }
    
    def _optimize_price_strategy(self, candidates: List[Dict[str, Any]], competitiveness: float) -> Dict[str, Any]:
        """优化价格策略（简化版本）"""

        try:
            # 这里应该使用Gurobi或其他优化算法
            # 暂时使用贪心算法作为示例
            
            # 按成本排序，选择成本最低且满足竞争力要求的方案
            candidates_df = pd.DataFrame(candidates)
            candidates_df = candidates_df.sort_values('payed_cost')
            
            # 计算总竞争力
            total_competitive = candidates_df['compete_gained'].sum()
            target_competitive = total_competitive * competitiveness
            
            # 选择满足竞争力要求的最低成本方案
            selected_candidates = []
            current_competitive = 0
            
            for _, candidate in candidates_df.iterrows():
                selected_candidates.append(candidate)
                current_competitive += candidate['compete_gained']
                
                if current_competitive >= target_competitive:
                    break
            
            logger.info(f"优化完成，选择了 {len(selected_candidates)} 个方案")
            
            return {
                'selected_candidates': selected_candidates,
                'total_cost': sum(c['payed_cost'] for c in selected_candidates),
                'total_competitive': current_competitive
            }
            
        except Exception as e:
            logger.error(f"价格策略优化失败: {str(e)}")
            raise
    
    def _apply_anti_inversion(self, solution: Dict[str, Any]) -> Dict[str, Any]:
        """应用防倒挂处理"""

        try:
            # 这里应该实现防倒挂逻辑
            # 暂时返回原方案
            logger.info("防倒挂处理完成")
            return solution
            
        except Exception as e:
            logger.error(f"防倒挂处理失败: {str(e)}")
            raise
    
    def _generate_result(self, solution: Dict[str, Any], product: str, competitiveness: float) -> Dict[str, Any]:
        """生成最终结果"""

        try:
            result = {
                'success': True,
                'product': product,
                'competitiveness': competitiveness,
                'data': pd.DataFrame(solution['selected_candidates']),
                'summary': {
                    'total_cost': solution['total_cost'],
                    'total_competitive': solution['total_competitive'],
                    'selected_count': len(solution['selected_candidates'])
                }
            }
            
            logger.info("结果生成完成")
            return result
            
        except Exception as e:
            logger.error(f"结果生成失败: {str(e)}")
            raise